import pandas as pd
from util import mysql_utils
from util import dict_data_utils

pool = mysql_utils.get_mysql_pool()

big_batch_dict = dict_data_utils.get_big_batch_dict()
small_batch_dict = dict_data_utils.get_small_batch_dict()
nature_name_dict = dict_data_utils.get_nature_name_dict()


# 根据 大批次、 小批次、 院校代码 查询院校是否存在
def query_university(big_batch, small_batch, code):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT id FROM university_2_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code}"
            cursor.execute(sql_query)
            result = cursor.fetchone()
            return result
    except Exception as e:
        print(f'查询院校基本信息异常：{e}')
        return None
    finally:
        conn.close()


def insert_university(insert_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                INSERT INTO university_2_new (
                    big_batch, small_batch, code, name, logo, province_code, city_code, province, city, level_name, 
                    telephone, site, enrol_site, nature_name, type_name, university_rank, university_rank_text, 
                    rank_score, esi_rank, f985, f211, dual_class_name, dual_class_A, dual_class_B, dual_class_subject, 
                    is_dual_class, is_vocational_college, is_backbone_college, competent_department, accept_add, 
                    enter_rule, jicha_2, jicha_3, jicha_4, jicha_5, jicha_6, change_difficult_level) 
                VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''', insert_values
            )
            conn.commit()
    except Exception as e:
        print(f'写入院校基本信息异常：{e}')
        return None
    finally:
        conn.close()


def update_university(update_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                UPDATE university_2_new SET
                    big_batch=%s, small_batch=%s, code=%s, name=%s, logo=%s, province_code=%s, city_code=%s, 
                    province=%s, city=%s, level_name=%s, telephone=%s, site=%s, enrol_site=%s, nature_name=%s,
                    type_name=%s, university_rank=%s, university_rank_text=%s, rank_score=%s, esi_rank=%s, f985=%s, 
                    f211=%s, dual_class_name=%s, dual_class_A=%s, dual_class_B=%s, dual_class_subject=%s, is_dual_class=%s, 
                    is_vocational_college=%s, is_backbone_college=%s, competent_department=%s, accept_add=%s,
                    enter_rule=%s, jicha_2=%s, jicha_3=%s, jicha_4=%s, jicha_5=%s, jicha_6=%s, change_difficult_level=%s
                    WHERE id=%s
                ''', update_values)
            conn.commit()
    except Exception as e:
        print(f'更新院校基本信息异常：{e}')
        return None
    finally:
        conn.close()


# 根据 大批次、 小批次、 院校代码 查询院校是否存在
def query_university_detail(big_batch, small_batch, code):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT id FROM university_2_detail_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code}"
            cursor.execute(sql_query)
            result = cursor.fetchone()
            return result
    except Exception as e:
        print(f'查询院校详情异常：{e}')
        return None
    finally:
        conn.close()


def insert_university_detail(insert_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                INSERT INTO university_2_detail_new (
                    big_batch, small_batch, code, name, introduction, second_round_class, first_round_class, 
                    fourth_discipline_evaluation, 20_wan_plan, wan_plan, special_major, change_rule) 
                VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''', insert_values
            )
            conn.commit()
    except Exception as e:
        print(f'写入院校详情异常：{e}')
        return None
    finally:
        conn.close()


def update_university_detail(update_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                UPDATE university_2_detail_new SET
                    big_batch=%s, small_batch=%s, code=%s, name=%s, introduction=%s, second_round_class=%s,
                    first_round_class=%s, fourth_discipline_evaluation=%s, 20_wan_plan=%s, wan_plan=%s, 
                    special_major=%s, change_rule=%s
                    WHERE id=%s
                ''', update_values)
            conn.commit()
    except Exception as e:
        print(f'更新院校详情异常：{e}')
        return None
    finally:
        conn.close()


def import_university():
    data = pd.read_excel("../static/excel_2024/交-院校信息（2023计划）-20240516.xlsx", header=0)
    for index, row in data.iterrows():
        print(index)

        if row['批次'] in big_batch_dict.keys():
            big_batch = big_batch_dict[row['批次']]
        else:
            big_batch = 0

        if row['小批次'] in small_batch_dict.keys():
            small_batch = small_batch_dict[row['小批次']]
        else:
            small_batch = 0

        if row['院校代码'] and not pd.isna(row['院校代码']):
            code = row['院校代码']
        else:
            code = ""

        if row['院校名称'] and not pd.isna(row['院校名称']):
            name = row['院校名称']
        else:
            name = ""

        if row['院校logo地址'] and not pd.isna(row['院校logo地址']):
            logo = row['院校logo地址']
        else:
            logo = ""

        if row['省代码'] and not pd.isna(row['省代码']):
            province_code = str(row['省代码']).split(".", maxsplit=1)[0]
        else:
            province_code = ""

        if row['市代码'] and not pd.isna(row['市代码']):
            city_code = str(row['市代码']).split(".", maxsplit=1)[0]
        else:
            city_code = ""

        if row['省份（计划）'] and not pd.isna(row['省份（计划）']):
            province = row['省份（计划）']
        else:
            province = ""

        if row['市（计划）'] and not pd.isna(row['市（计划）']):
            city = row['市（计划）']
        else:
            city = ""

        if row['培养层次'] and not pd.isna(row['培养层次']):
            level_name = row['培养层次']
        else:
            level_name = ""

        if row['咨询电话'] and not pd.isna(row['咨询电话']):
            telephone = row['咨询电话']
        else:
            telephone = ""

        if row['院校官网'] and not pd.isna(row['院校官网']):
            site = row['院校官网']
        else:
            site = ""

        if row['院校招生考试网'] and not pd.isna(row['院校招生考试网']):
            enrol_site = row['院校招生考试网']
        else:
            enrol_site = ""

        if row['性质'] in nature_name_dict.keys():
            nature_name = nature_name_dict[row['性质']]
        else:
            nature_name = 0

        if row['院校类型'] and not pd.isna(row['院校类型']):
            type_name = row['院校类型']
        else:
            type_name = ""

        if row['软科全国排名'] and not pd.isna(row['软科全国排名']) and (row['软科全国排名'] != "0.0" or row['软科全国排名'] != "0") and (
                row['软科全国排名'] != 0.0 or row['软科全国排名'] != 0):
            try:
                university_rank = int(row['软科全国排名'])
            except Exception as e:
                print(f'软科全国排名取数异常：{e}')
                university_rank = 999
        else:
            university_rank = 999

        if row['软科分类排名'] and not pd.isna(row['软科分类排名']):
            university_rank_text = row['软科分类排名']
        else:
            university_rank_text = ""

        if row['软科总分'] and not pd.isna(row['软科总分']):
            rank_score = row['软科总分']
        else:
            rank_score = ""

        if row['ESI国际排名'] and not pd.isna(row['ESI国际排名']):
            esi_rank = row['ESI国际排名']
        else:
            esi_rank = ""

        if row['是否985'] == "985.0" or row['是否985'] == "985" or row['是否985'] == 985.0 or row['是否985'] == 985:
            f985 = 1
        else:
            f985 = 0

        if row['是否211'] == "211.0" or row['是否211'] == "211" or row['是否211'] == 211.0 or row['是否211'] == 211:
            f211 = 1
        else:
            f211 = 0

        if row['双一流标签'] and not pd.isna(row['双一流标签']):
            dual_class_name = row['双一流标签']
        else:
            dual_class_name = ""

        if row['是否一流大学A类'] == "1.0" or row['是否一流大学A类'] == "1" or row['是否一流大学A类'] == 1.0 or row['是否一流大学A类'] == 1:
            dual_class_A = 1
        else:
            dual_class_A = 0

        if row['是否一流大学B类'] == "1.0" or row['是否一流大学B类'] == "1" or row['是否一流大学B类'] == 1.0 or row['是否一流大学B类'] == 1:
            dual_class_B = 1
        else:
            dual_class_B = 0

        if row['是否一流学科大学'] == "1.0" or row['是否一流学科大学'] == "1" or row['是否一流学科大学'] == 1.0 or row['是否一流学科大学'] == 1:
            dual_class_subject = 1
        else:
            dual_class_subject = 0

        if row['是否双高'] == "双高":
            is_dual_class = 1
        else:
            is_dual_class = 0

        if row['是否示范性高职院校'] == "示范性高职院校":
            is_vocational_college = 1
        else:
            is_vocational_college = 0

        if row['是否骨干高职院校'] == "骨干高职院校":
            is_backbone_college = 1
        else:
            is_backbone_college = 0

        if row['主管部门'] and not pd.isna(row['主管部门']):
            competent_department = row['主管部门']
        else:
            competent_department = ""

        if row['录取是否认可加分'] and not pd.isna(row['录取是否认可加分']):
            accept_add = row['录取是否认可加分']
        else:
            accept_add = ""

        if row['录取规则'] and not pd.isna(row['录取规则']):
            enter_rule = row['录取规则']
        else:
            enter_rule = ""

        if row['级差2'] and not pd.isna(row['级差2']):
            jicha_2 = str(row['级差2'])
        else:
            jicha_2 = ''

        if row['级差3'] and not pd.isna(row['级差3']):
            jicha_3 = str(row['级差3'])
        else:
            jicha_3 = ''

        if row['级差4'] and not pd.isna(row['级差4']):
            jicha_4 = str(row['级差4'])
        else:
            jicha_4 = ''

        if row['级差5'] and not pd.isna(row['级差5']):
            jicha_5 = str(row['级差5'])
        else:
            jicha_5 = ''

        if row['级差6'] and not pd.isna(row['级差6']):
            jicha_6 = str(row['级差6'])
        else:
            jicha_6 = ''

        if row['转专业困难程度'] and not pd.isna(row['转专业困难程度']):
            change_difficult_level = row['转专业困难程度']
        else:
            change_difficult_level = ""

        if row['院校简介'] and not pd.isna(row['院校简介']):
            introduction = row['院校简介']
        else:
            introduction = ""

        if row['第二轮双一流学科'] and not pd.isna(row['第二轮双一流学科']):
            second_round_class = row['第二轮双一流学科']
        else:
            second_round_class = ""

        if row['第一轮双一流学科'] and not pd.isna(row['第一轮双一流学科']):
            first_round_class = row['第一轮双一流学科']
        else:
            first_round_class = ""

        if row['第四学科评估'] and not pd.isna(row['第四学科评估']):
            fourth_discipline_evaluation = row['第四学科评估']
        else:
            fourth_discipline_evaluation = ""

        if row['双万计划（国家级）'] and not pd.isna(row['双万计划（国家级）']):
            wan_plan_20 = row['双万计划（国家级）']
        else:
            wan_plan_20 = ""

        if row['双万计划（省级）'] and not pd.isna(row['双万计划（省级）']):
            wan_plan = row['双万计划（省级）']
        else:
            wan_plan = ""

        if row['学校特色专业'] and not pd.isna(row['学校特色专业']):
            special_major = row['学校特色专业']
        else:
            special_major = ""

        if row['转专业规则'] and not pd.isna(row['转专业规则']):
            change_rule = row['转专业规则']
        else:
            change_rule = ""

        is_exist = query_university(big_batch, small_batch, code)
        print(f'院校基础信息：{is_exist}')
        if is_exist:
            update_value = (
                big_batch, small_batch, code, name, logo, province_code, city_code, province, city, level_name,
                telephone, site, enrol_site, nature_name, type_name, university_rank, university_rank_text, rank_score,
                esi_rank, f985, f211, dual_class_name, dual_class_A, dual_class_B, dual_class_subject, is_dual_class,
                is_vocational_college, is_backbone_college, competent_department, accept_add, enter_rule, jicha_2,
                jicha_3, jicha_4, jicha_5, jicha_6, change_difficult_level, is_exist[0]
            )
            print(update_value)
            update_university(update_value)
        else:
            insert_value = (
                big_batch, small_batch, code, name, logo, province_code, city_code, province, city, level_name,
                telephone, site, enrol_site, nature_name, type_name, university_rank, university_rank_text, rank_score,
                esi_rank, f985, f211, dual_class_name, dual_class_A, dual_class_B, dual_class_subject, is_dual_class,
                is_vocational_college, is_backbone_college, competent_department, accept_add, enter_rule, jicha_2,
                jicha_3, jicha_4, jicha_5, jicha_6, change_difficult_level
            )

            print(insert_value)
            insert_university(insert_value)

        detail_is_exist = query_university_detail(big_batch, small_batch, code)
        print(f'院校详情信息：{detail_is_exist}')
        if detail_is_exist:
            detail_update_value = (
                big_batch, small_batch, code, name, introduction, second_round_class, first_round_class,
                fourth_discipline_evaluation, wan_plan_20, wan_plan, special_major, change_rule, detail_is_exist[0]
            )
            print(detail_update_value)
            update_university_detail(detail_update_value)
        else:
            detail_insert_value = (
                big_batch, small_batch, code, name, introduction, second_round_class, first_round_class,
                fourth_discipline_evaluation, wan_plan_20, wan_plan, special_major, change_rule
            )

            print(detail_insert_value)
            insert_university_detail(detail_insert_value)


if __name__ == "__main__":
    import_university()
